from ..database import connect_to_database, close_database_connection


def get_foods_info():
    db, ssh_tunnel = connect_to_database()
    query = """
    SELECT f.id, f.name, c.name AS category, f.calories, f.unit, f.protein, f.fat, f.carbs, f.img_url
    FROM foods f
    JOIN categories c ON f.category_id = c.id
    """
    try:
        with db.cursor() as cursor:
            cursor.execute(query)
            results = cursor.fetchall()

            if not results:
                return None

            columns = [
                "id",
                "name",
                "category",  # 这里改为 category 来表示类别名称
                "calories",
                "unit",
                "protein",
                "fat",
                "carbs",
                "img_url",
            ]
            foods_data = [dict(zip(columns, food)) for food in results]

            return foods_data
    except Exception as e:
        raise Exception(f"Database query error: {e}")
    finally:
        close_database_connection(db, ssh_tunnel)
